package com.itmk.student_select.dao.impl;

import com.itmk.common.BaseDao;
import com.itmk.student_select.dao.StudentSelectDao;
import com.itmk.student_select.entity.StudentSelectParm;
import com.itmk.student_select.entity.StudentSelectVo;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.StringUtils;

import java.sql.SQLException;
import java.util.List;

public class StudentSelectDaoImpl extends BaseDao implements StudentSelectDao {
    @Override
    public List<StudentSelectVo> list(StudentSelectParm parm) {
        //1、定义sql

        StringBuilder sb = new StringBuilder("select cu.*,mj.major_name,cg.colloge_name,ta.teacher_id,ta.teacher_name from course as cu left join major as mj on mj.major_id = cu.major_id  left join colloge as cg on cg.colloge_id = mj.colloge_id \n" +
                "left join course_teacher as ct on ct.course_id = cu.course_id  left join teacher as ta on ta.teacher_id = ct.teacher_id");
        //关键字处理
        if(StringUtils.isNotEmpty(parm.getCourseName())){
            sb.append(" where cu.course_name like '%" + parm.getCourseName() +"%'");
        }
        //分页
        sb.append(" limit ?,?");
        String sql = sb.toString();
        //2、封装参数
        Object[] objects = {
                (parm.getPage()-1)*parm.getLimit(),
                parm.getLimit()
        };
        //3、定义hangdler
        BeanListHandler<StudentSelectVo> handler = new BeanListHandler<>(StudentSelectVo.class,processor);
        try {
            return query.query(sql,handler,objects);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int getCount(StudentSelectParm parm) {
         //1、定义sql

        StringBuilder sb = new StringBuilder("select count(cu.course_id) from course as cu left join major as mj on " +
                "mj.major_id = cu.major_id  left join colloge as cg on cg.colloge_id = mj.colloge_id \n" +
                "left join course_teacher as ct on ct.course_id = cu.course_id  left join teacher as ta on ta.teacher_id = ct.teacher_id");
        //关键字处理
        if(StringUtils.isNotEmpty(parm.getCourseName())){
            sb.append(" where cu.course_name like '%" + parm.getCourseName() +"%'");
        }
        String sql = sb.toString();
        //2、定义handler
        ScalarHandler handler = new ScalarHandler();
        try {
            return Integer.valueOf(query.query(sql,handler).toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
}
